<?php
/**
 * This file is part of the Agora distribution. 
 * Detailed copyright and licensing information can be found
 * in the gpl-3.0.txt file which should be included in the distribution.
 * 
 * @version		$Id: 3.0.14 Olympus 2010-09-28 rammstein4o $
 * @copyright  2007 - 2010 jVitals
 * @license   GPLv3 Open Source
 * @link       http://jvitals.com
 * @since      File available since initial release
 */
	defined ('IN_AGORA') or die;

	class SearchModel extends Model
	{
		var $_level;

		function __construct()
		{
			parent::__construct('posts');
		}

		function loadForumChildren(& $raw_data, $parent, & $result)
		{
			if (is_null($parent)) $parent = 0;

			foreach ($raw_data as $forum) {
				if ($forum['parent_forum_id'] == $parent) {

					$this_children = array();
					$this->_level++;
					$this->loadForumChildren($raw_data, $forum['id'],$this_children);
					$this->_level--;

					$forum['level'] = $this->_level;
					
					$cat = $forum['cat_name'];
					unset($forum['cat_name']);
					unset($forum['parent_forum_id']);

					if ($parent == 0) {

						$result[$cat][] = $forum;
						$result[$cat] = array_merge($result[$cat],$this_children);
					} else {
						$result[] = $forum;
						$result = array_merge($result,$this_children);
					}


				}
			}
		}

		function loadForumList()
		{
			$this->db->setQuery("SELECT f.id, f.parent_forum_id, f.forum_name,c.cat_name FROM".
						" {$this->prefix}forums AS f".
						" INNER JOIN {$this->prefix}categories AS c".
						" ON f.cat_id = c.id");

			$forum = $this->db->loadAssocList();

			$result = array();

			$this->_level = 0;
			$this->loadForumChildren($forum,0,$result);
			return $result;
		}

		function count_latest($hrs)
		{
			$match_sql = 't.last_post > '.(AGORA_TIME - $hrs*3600);
			$sql = "SELECT COUNT(DISTINCT t.id)".
				' FROM ##__posts AS p '.
				' INNER JOIN ##__topics AS t'.
				' ON p.topic_id = t.id'.
				' INNER JOIN ##__forums AS f'.
				' ON t.forum_id = f.id'.
				" WHERE $match_sql AND t.approved = 1 ";
			$this->db->setQuery($sql);
			return $this->db->loadResult();
		}

		function getLatest($hrs,$per_page = null, $page = null)
		{
			$limit = $this->getLimit($per_page, $page);

			$fields = 't.id AS id,'.
					' t.poster,'.
					' t.subject,'.
					' t.question,'.
					' t.last_post,'.
					' t.last_post_id,'.
					' t.last_poster,'.
					' t.num_replies,'.
					' t.num_views,'.
					' t.closed,'.
					' t.sticky,'.
					' t.forum_id,'.
					' p.message as postcontent';

			$group_by_sql = ' GROUP BY t.id, t.poster, t.subject, t.last_post, t.last_post_id, t.last_poster, t.num_replies, t.closed, t.forum_id, t.sticky';
			$sort_by_sql = 't.last_post';
			$match_sql = 't.last_post > '.(AGORA_TIME - $hrs*3600);
			$sql = "SELECT $fields".
				' FROM ##__posts AS p '.
				' INNER JOIN ##__topics AS t'.
				' ON p.topic_id = t.id'.
				' INNER JOIN ##__forums AS f'.
				' ON t.forum_id = f.id'.
				" WHERE $match_sql AND t.approved = 1 $group_by_sql ORDER BY $sort_by_sql DESC";
			$sql .= $limit;

			$this->db->setQuery($sql);
			return $this->db->loadAssocList();
		}
		
		function getUnreaded($per_page = null, $page = null, $user_id) {
			$this->db->bind('user_id',$user_id,'integer');
			$limit = $this->getLimit($per_page, $page);
			
			$sql = 'SELECT t.*, p.message as postcontent ' .
				' FROM ##__topics AS t ' . 
				' LEFT JOIN ##__read_posts AS r ON t.id = r.topic_id AND r.user_id = :user_id ' .
				' LEFT JOIN ##__posts AS p ON t.id = p.topic_id ' .
				' WHERE t.approved = 1 AND (r.id IS NULL OR t.last_post > r.read_time) ' . 
				' ORDER BY t.posted DESC '
				. $limit;
			$this->db->setQuery($sql);
			
			return $this->db->loadAssocList();
		}

		function count_unreaded($user_id)
		{
            $this->db->bind('user_id',$user_id,'integer');
            $sql2=
                'SELECT COUNT(*)'.
                ' FROM ##__topics AS topic'.
                ' LEFT JOIN ##__read_posts AS read_p'.
                '  ON topic.id = read_p.topic_id'.
                ' WHERE topic.approved = 1 AND read_p.user_id = :user_id'.
                '  AND read_p.read_time >= topic.last_post'
            ;        
			$this->db->setQuery($sql2);
			$res = $this->db->loadResult();

            $sql=
                'SELECT COUNT(*)'.
                ' FROM ##__topics AS topic WHERE approved = 1';        
			$this->db->setQuery($sql);
			$res2 = $this->db->loadResult();
			
			return $res2-$res;
		}
		
		function count_unanswered()
		{
			$match_sql = 't.num_replies = 0 AND t.approved = 1';
			$sql = 'SELECT COUNT(*)'.
				' FROM ##__topics AS t '.
				' INNER JOIN ##__forums AS f'.
				' ON t.forum_id = f.id'.
				" WHERE $match_sql";

			$this->db->setQuery($sql);
			return $this->db->loadResult();
		}

		function getUnanswered($per_page = null, $page = null)
		{
			$limit = $this->getLimit($per_page, $page);

			$fields = 't.id AS id,'.
					' t.poster,'.
					' t.subject,'.
					' t.question,'.
					' t.last_post,'.
					' t.last_post_id,'.
					' t.last_poster,'.
					' t.num_replies,'.
					' t.num_views,'.
					' t.closed,'.
					' t.sticky,'.
					' t.forum_id,'.
					' p.message as postcontent';

			$group_by_sql = ' GROUP BY t.id, t.poster, t.subject, t.last_post, t.last_post_id, t.last_poster, t.num_replies, t.closed, t.forum_id, t.sticky';
			$sort_by_sql = 't.posted';
			$match_sql = 't.num_replies = 0 AND t.approved = 1';
			$sql = "SELECT $fields".
				' FROM ##__topics AS t '.
				' INNER JOIN ##__forums AS f'.
				' ON t.forum_id = f.id'.
				' INNER JOIN ##__posts AS p'.
				' ON p.topic_id = t.id'.
				" WHERE $match_sql $group_by_sql ORDER BY $sort_by_sql DESC";
			$sql .= $limit;

			$this->db->setQuery($sql);
			return $this->db->loadAssocList();
		}

		function search($params,$per_page = null, $page = null, $allowed_forums=array() )
		{
			$limit = $this->getLimit($per_page, $page);
			
			$keywords = $this->db->Quote($params['keywords']);
			$author = $this->db->Quote($params['author']);
			$sort_by = (int)$params['sort_by'];
			$forum = $this->db->Quote($params['forum']);
			$show_as = $params['show_as'];
			$sort_dir = $params['sort_dir'] == 'DESC' ? 'DESC' : 'ASC';

			$fields = '';
			$group_by_sql = '';

			$forum_sql = ($params['forum'] != -1) ? " AND f.forum_name = ".$forum : '';

			switch ($sort_by)
			{
				case 1:
					$sort_by_sql = ($show_as == 'topics') ? 't.poster' : 'p.poster';
					break;
				case 2:
					$sort_by_sql = 't.subject';
					break;
				case 3:
					$sort_by_sql = 't.forum_id';
					break;
				default:
					{
						$sort_by_sql = ($show_as == 'topics') ? 't.posted' : 'p.posted';
						if ($show_as == 'topics')
							$group_by_sql = ', t.posted';
						break;
					}
			}

			if(!empty($params['author']) && !empty($params['keywords'])) {
				$author_sql = " AND p.poster = $author";
			} else {
				$author_sql = '';
			}

			$select_sql = '';
			$group_by_sql = '';

			if ($show_as == 'posts') {

				if (!empty($params['keywords'])) {
					$match_msg = "MATCH (p.message) AGAINST ($keywords)";

					if ($sort_by == 5) {
						$sort_by_sql = 'score';
					}

					$fields = "$match_msg AS score, ";
					$match_sql = $match_msg;
				} else {
					// Author search
					$match_sql = "p.poster = $author";

					if($sort_by == 5)
						$sort_by_sql = 'p.posted';
				}

				$fields .= 'p.id AS pid,'.
						' p.poster AS pposter,'.
						' p.posted AS pposted,'.
						' p.poster_id,'.
						' SUBSTRING(p.message, 1, 1000) AS message,'.
						' t.id AS tid,'.
						' t.poster,'.
						' t.subject,'.
						' t.question,'.
						' t.last_post,'.
						' t.last_post_id,'.
						' t.last_poster,'.
						' t.num_replies,'.
						' t.forum_id';
			} else {
				if(!empty($params['keywords']))
				{
					// Keyword search
					$match_msg = "MATCH (p.message) AGAINST ($keywords)";
					$match_subject = "MATCH (t.subject) AGAINST ($keywords)";

					if($sort_by == 5)
						$sort_by_sql = 'score';

					$fields = $match_msg.' + '.$match_subject.' AS score, ';
					$match_sql = "($match_msg OR $match_subject)";
				}
				else
				{
					// Author search
					$match_sql = "p.poster = $author";

					if($sort_by == 5)
						$sort_by_sql = 't.posted';
				}

				$fields .= 't.id AS id,'.
						' t.poster,'.
						' t.subject,'.
						' t.question,'.
						' t.last_post,'.
						' t.last_post_id,'.
						' t.last_poster,'.
						' t.num_replies,'.
						' t.num_views,'.
						' t.closed,'.
						' t.forum_id,'.
						' p.message as postcontent';
				$group_by_sql = ' GROUP BY t.id, t.poster, t.subject, t.last_post, t.last_post_id, t.last_poster, t.num_replies, t.closed, t.forum_id'.$group_by_sql;
			}

			if ($allowed_forums) {
				$this->db->bindList('forums',$allowed_forums,'integer');
				$where = ' t.forum_id IN (:forums) AND';
			} else {
				$where = '';
			}
			$where .= ' p.approved = 1 AND ';

			$sql = "SELECT $fields".
				" FROM {$this->prefix}posts AS p ".
				" INNER JOIN {$this->prefix}topics AS t".
				" ON p.topic_id = t.id".
				" INNER JOIN {$this->prefix}forums AS f".
				" ON t.forum_id = f.id".
				" WHERE $where $match_sql $forum_sql $author_sql $group_by_sql ORDER BY $sort_by_sql $sort_dir";
			$sql .= $limit;
			$this->db->setQuery($sql);
			
			return $this->db->loadAssocList();

		}
		
		function count_search($params)
		{
			
			$keywords = $this->db->Quote($params['keywords']);
			$author = $this->db->Quote($params['author']);
			$sort_by = (int)$params['sort_by'];
			$forum = $this->db->Quote($params['forum']);
			$show_as = $params['show_as'];
			$sort_dir = $params['sort_dir'] == 'DESC' ? 'DESC' : 'ASC';

			$fields = '';
			$group_by_sql = '';

			$forum_sql = ($params['forum'] != -1) ? " AND f.forum_name = ".$forum : '';

			switch ($sort_by)
			{
				case 1:
					$sort_by_sql = ($show_as == 'topics') ? 't.poster' : 'p.poster';
					break;
				case 2:
					$sort_by_sql = 't.subject';
					break;
				case 3:
					$sort_by_sql = 't.forum_id';
					break;
				default:
					{
						$sort_by_sql = ($show_as == 'topics') ? 't.posted' : 'p.posted';
						if ($show_as == 'topics')
							$group_by_sql = ', t.posted';
						break;
					}
			}

			if(!empty($params['author']) && !empty($params['keywords'])) {
				$author_sql = " AND p.poster = $author";
			} else {
				$author_sql = '';
			}

			$select_sql = '';
			$group_by_sql = '';

			if ($show_as == 'posts') {

				if (!empty($params['keywords'])) {
					$match_msg = "MATCH (p.message) AGAINST ($keywords)";

					if ($sort_by == 5) {
						$sort_by_sql = 'score';
					}

					$fields = "$match_msg AS score, ";
					$match_sql = $match_msg;
				} else {
					// Author search
					$match_sql = "p.poster = $author";

					if($sort_by == 5)
						$sort_by_sql = 'p.posted';
				}

				$fields .= 'p.id AS pid,'.
						' p.poster AS pposter,'.
						' p.posted AS pposted,'.
						' p.poster_id,'.
						' SUBSTRING(p.message, 1, 1000) AS message,'.
						' t.id AS tid,'.
						' t.poster,'.
						' t.subject,'.
						' t.question,'.
						' t.last_post,'.
						' t.last_post_id,'.
						' t.last_poster,'.
						' t.num_replies,'.
						' t.forum_id';
			} else {
				if(!empty($params['keywords']))
				{
					// Keyword search
					$match_msg = "MATCH (p.message) AGAINST ($keywords)";
					$match_subject = "MATCH (t.subject) AGAINST ($keywords)";

					if($sort_by == 5)
						$sort_by_sql = 'score';

					$fields = $match_msg.' + '.$match_subject.' AS score, ';
					$match_sql = "($match_msg OR $match_subject)";
				}
				else
				{
					// Author search
					$match_sql = "p.poster = $author";

					if($sort_by == 5)
						$sort_by_sql = 't.posted';
				}

				$fields .= 't.id AS id,'.
						' t.poster,'.
						' t.subject,'.
						' t.question,'.
						' t.last_post,'.
						' t.last_post_id,'.
						' t.last_poster,'.
						' t.num_replies,'.
						' t.num_views,'.
						' t.closed,'.
						' t.forum_id';
				$group_by_sql = ' GROUP BY t.id, t.poster, t.subject, t.last_post, t.last_post_id, t.last_poster, t.num_replies, t.closed, t.forum_id'.$group_by_sql;
			}


			$sql = "SELECT $fields".
				" FROM {$this->prefix}posts AS p ".
				" INNER JOIN {$this->prefix}topics AS t".
				" ON p.topic_id = t.id".
				" INNER JOIN {$this->prefix}forums AS f".
				" ON t.forum_id = f.id".
				" WHERE p.approved = 1 AND $match_sql $forum_sql $author_sql $group_by_sql ORDER BY $sort_by_sql $sort_dir";
			
			
			$this->db->setQuery($sql);
			$res = $this->db->loadAssocList();

			return $this->db->getAffectedRows();
			 
		}
	}
?>